const request=require("superagent");
const https = require('https');
const cheerio = require('cheerio');
const iconv = require('iconv-lite');
const util = require('util');
const charset = require("superagent-charset")(request);
const fs=require('fs');
var mysql      = require('mysql');
const assert = require('assert');
var connection = mysql.createConnection({
  host     : '127.0.0.1',
  user     : 'root',
  password : '123456',
  database : 'ln_matter'
});
connection.query = util.promisify(connection.query);
async function getResourceTree(page) {
    var resource = await page._client.send('Page.getResourceTree');
    return resource.frameTree;
}

async function getResourceContent(page, url) {
    const { content, base64Encoded } = await page._client.send(
        'Page.getResourceContent',
        { frameId: String(page.mainFrame()._id), url },
    );
    assert.equal(base64Encoded, true);
    return content;
};
const Agent=[
 	"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36",
    "Mozilla/5.0 (Linux; U; Android 2.3.7; en-us; Nexus One Build/FRF91) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "MQQBrowser/26 Mozilla/5.0 (Linux; U; Android 2.3.7; zh-cn; MB200 Build/GRJ22; CyanogenMod-7) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1",
    "JUC (Linux; U; 2.3.7; zh-cn; MB200; 320*480) UCWEB7.9.3.103/139/999",
    "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:7.0a1) Gecko/20110623 Firefox/7.0a1 Fennec/7.0a1",
    "Opera/9.80 (Android 2.3.4; Linux; Opera Mobi/build-1107180945; U; en-GB) Presto/2.8.149 Version/11.10",
    "Mozilla/5.0 (Linux; U; Android 3.0; en-us; Xoom Build/HRI39) AppleWebKit/534.13 (KHTML, like Gecko) Version/4.0 Safari/534.13",
    "Mozilla/5.0 (iPhone; U; CPU iPhone OS 3_0 like Mac OS X; en-us) AppleWebKit/420.1 (KHTML, like Gecko) Version/3.0 Mobile/1A542a Safari/419.3",
    "Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_0 like Mac OS X; en-us) AppleWebKit/532.9 (KHTML, like Gecko) Version/4.0.5 Mobile/8A293 Safari/6531.22.7",
    "Mozilla/5.0 (iPad; U; CPU OS 3_2 like Mac OS X; en-us) AppleWebKit/531.21.10 (KHTML, like Gecko) Version/4.0.4 Mobile/7B334b Safari/531.21.10",
    "Mozilla/5.0 (BlackBerry; U; BlackBerry 9800; en) AppleWebKit/534.1+ (KHTML, like Gecko) Version/6.0.0.337 Mobile Safari/534.1+",
    "Mozilla/5.0 (hp-tablet; Linux; hpwOS/3.0.0; U; en-US) AppleWebKit/534.6 (KHTML, like Gecko) wOSBrowser/233.70 Safari/534.6 TouchPad/1.0",
    "Mozilla/5.0 (SymbianOS/9.4; Series60/5.0 NokiaN97-1/20.0.019; Profile/MIDP-2.1 Configuration/CLDC-1.1) AppleWebKit/525 (KHTML, like Gecko) BrowserNG/7.1.18124",
    "Mozilla/5.0 (compatible; MSIE 9.0; Windows Phone OS 7.5; Trident/5.0; IEMobile/9.0; HTC; Titan)",
    "Mozilla/5.0 (Linux; U; Android 8.1.0; zh-cn; BLA-AL00 Build/HUAWEIBLA-AL00) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/57.0.2987.132 MQQBrowser/8.9 Mobile Safari/537.36",
];
const timeout={
	response:50000,
	deadline:600000,
}
Date.prototype.format = function (fmt) {
    var o = {
        "M+": this.getMonth() + 1, //月份
        "d+": this.getDate(), //日
        "h+": this.getHours(), //小时
        "m+": this.getMinutes(), //分
        "s+": this.getSeconds(), //秒
        "q+": Math.floor((this.getMonth() + 3) / 3), //季度
        "S": this.getMilliseconds() //毫秒
    };
    if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o)
        if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
    return fmt;
}
function delay(t){
    return new Promise(resolve => setTimeout(resolve, t))
}

function rnd(n, m){
    var random = Math.floor(Math.random()*(m-n+1)+n);
    return random;
}
function getRndIP(){
    var ip=[];
    for(var i=0;i<4;i++){
    ip.push(rnd(10,250));
    }
    return ip.join(".");
}

const agent_c = new https.Agent({
  rejectUnauthorized: false
});

// process.env.NODE_TLS_REJECT_UNAUTHORIZED = "0";
//封装请求 实现失败重试
async function requestWithRetry(url, retryCount = 3, timeout = 5000,option) {
  return new Promise((resolve, reject) => {
    let numRetries = 0;
 
    const makeRequest = () => {
      request
        .get(url)
        .set(option)
        .agent(agent_c)
        .timeout(timeout) // 设置超时时间
        .then(response => {
          resolve(response);
        })
        .catch(error => {
          if (error.code === 'ECONNABORTED' && numRetries < retryCount) {
            numRetries += 1;
            console.log(`Request timed out. Retrying... (Attempt ${numRetries})`);
            makeRequest(); // 重试请求
          } else {
            reject(error);
          }
        });
    };
 
    makeRequest();
  });
}
async function getArea(){
    let level_i = 1;
    let sql=`select * from m_area where id > 0 AND level = `+level_i;
        let result=await connection.query(sql);
        for (var i = 0; i < result.length; i++) {
            var ip=getRndIP();
            var agent=Agent[rnd(0,Agent.length-1)];
            agent=Agent[0];
            var option={
                "Host":"www.lnzwfw.gov.cn",
                "User-Agent":agent,
                "Referer":'https://www.lnzwfw.gov.cn/',
                "X-Forwarded-For":ip,
            }
            let url=`https://www.lnzwfw.gov.cn/matter/areaMenu/list?areaCode=`+result[i]['code'];
            console.log(result[i]['id'],result[i]['name'],url);
            let res = await requestWithRetry(url, 3, 5000,option).then(response => {
                // console.log('Response received:', response);
                return response;
              }).catch(error => {
                console.error('Error:', error);
              });
            let resData = JSON.parse(res.text).data;
            for (var j = 0; j < resData.length; j++) {
                await asySql("INSERT INTO m_area SET  ?",{
                        'pid' : result[i]['id'],
                        'code' : resData[j]['areaCode'],
                        'name' : resData[j]['areaName'],
                        'level' : level_i+1,
                        'url': resData[j]['url']
                    }).then(insertId => {
                        return insertId;
                    }).catch(error => {
                      console.error(error); // 异步操作失败，输出错误信息
                    });
            }
            await delay(500);
            
        }
    		
}

async function getUnit(){
    let serverType = 1;
    let sql=`select * from m_area where id > 0 AND id = 40`;
    let result=await connection.query(sql);
    for (var i = 0; i < result.length; i++) {
            var ip=getRndIP();
            var agent=Agent[rnd(0,Agent.length-1)];
            agent=Agent[0];
            var option={
                "Host":"www.lnzwfw.gov.cn",
                "User-Agent":agent,
                "Referer":'https://www.lnzwfw.gov.cn/',
                "X-Forwarded-For":ip,
            }
            let url=`https://zwfw.dl.gov.cn/dlPortal/getCategoryByItem?areacode=${result[i]['code']}&serverType=${serverType}`;
            console.log(result[i]['id'],result[i]['name'],url);
            let res = await requestWithRetry(url, 3, 5000,option).then(response => {
                // console.log('Response received:', response);
                return response;
              }).catch(error => {
                console.error('Error:', error);
              });
            let resData = JSON.parse(res.text);

            //部门
            for (let key1 in resData.orgMap) {
                  if (resData.orgMap.hasOwnProperty(key1)) { // 确保是对象自有属性，不是原型链上的属性
                    console.log(key1, resData.orgMap[key1]);
                    let checkResult = await connection.query(`select * from m_unit where code = '${key1}'`);
                    if(checkResult.length>0){
                        console.error('数据已存在', resData.orgMap[key1]);
                    }else{
                        await asySql("INSERT INTO m_unit SET  ?",{
                            'area_id' : result[i]['id'],
                            'code' : key1,
                            'name' : resData.orgMap[key1]
                        }).then(insertId => {
                            return insertId;
                        }).catch(error => {
                          console.error(error); // 异步操作失败，输出错误信息
                        });
                    }
                  }
            }
            
            await delay(1000);
            
        }
        
          
process.exit(0);  
}

async function getMatterList(){
    let serverType = 1;
    let sql=`select * from m_area where id > 0 AND id = 40`;
    let result=await connection.query(sql);
    for (var i = 0; i < result.length; i++) {

        for (var pageNumber = 1; pageNumber <= 93; pageNumber++) {
            var ip=getRndIP();
            var agent=Agent[rnd(0,Agent.length-1)];
            agent=Agent[0];
            var option={
                "Host":"www.lnzwfw.gov.cn",
                "User-Agent":agent,
                "Referer":'https://www.lnzwfw.gov.cn/',
                "X-Forwarded-For":ip,
            }
            let url=`https://zwfw.dl.gov.cn/dlPortal/item/itemList?areacode=${result[i]['code']}&serverType=${serverType}&pageNumber=${pageNumber}&pageSize=5&taskType&stKeyword&isIndex=Y`;
            console.log(pageNumber,result[i]['id'],result[i]['name'],url);
            let res = await requestWithRetry(url, 3, 5000,option).then(response => {
                // console.log('Response received:', response);
                return response;
              }).catch(error => {
                console.error('Error:', error);
              });
            let resData = JSON.parse(res.text);
            resData = resData.itemList;

             for (var j = 0; j < resData.length; j++) {
                //部门数据
                let unitResult = await connection.query(`select * from m_unit where code = '${resData[j]['DeptCode']}'`);
                 // console.log(unitResult[0]['full_name'])
                let unit_id = 0;
                //判断部门是否已存在
                if(unitResult.length>0){
                    //存在
                    //判断部门全称是否存在
                    if(unitResult[0]['full_name']==null){
                        //不存在 插入部门全称
                        console.log('插入部门全称',resData[j]['OrgName'])
                        await connection.query(`update m_unit SET full_name = '${resData[j]['OrgName']}' where id = '${unitResult[0]['id']}'`);
                    }
                    unit_id = unitResult[0]['id'];
                }else{
                    //不存在 插入部门
                    console.log('插入部门',resData[j]['OrgName'])
                    let addSql="INSERT INTO m_unit SET  ?";
                    let addSqlParams = {
                        'area_id':result[i]['id'],
                        'name':resData[j]['OrgShortName'],
                        'code':resData[j]['DeptCode'],
                        'full_name':resData[j]['OrgName']
                    };
                    unit_id = await asySql(addSql,addSqlParams).then(insertId => {
                        return insertId;
                    }).catch(error => {
                      console.error(error); // 异步操作失败，输出错误信息
                    });

                }
                if(unit_id>0){
                    //事项目录
                    let directory_id = 0;
                    let directoryResult = await connection.query(`select * from m_matter_directory where TaskCode = '${resData[j]['TaskCode']}'`);
                    if(directoryResult.length>0){
                        directory_id = directoryResult[0]['id'];
                        console.error('目录数据已存在', directoryResult[0]['name']);
                    }else{
                        directory_id = await asySql("INSERT INTO m_matter_directory SET  ?",{
                                'TaskCode' : resData[j]['TaskCode'],
                                'name' : resData[j]['SubTaskName'],
                                'area_id': result[i]['id'],
                                'unit_id': unit_id
                            }).then(insertId => {
                                return insertId;
                            }).catch(error => {
                              console.error(error); // 异步操作失败，输出错误信息
                            });
                    }
                    let itemList = resData[j]['itemList'];
                    //事项列表
                    for(var k = 0; k < itemList.length; k++) {
                        let checkResult = await connection.query(`select * from m_matter where ITEM_ID = '${itemList[k]['ITEM_ID']}'`);
                        if(checkResult.length>0){
                            console.error('事项数据已存在', checkResult[0]['name']);
                        }else{
                            await asySql("INSERT INTO m_matter SET  ?",{
                                    'ITEM_ID' : itemList[k]['ITEM_ID'],
                                    'implement_code': itemList[k]['TaskHandleItem'],
                                    'LinkAddr':itemList[k]['LinkAddr'],
                                    'name':itemList[k]['TaskName'],
                                    'directory_id':directory_id,
                                    'area_id': result[i]['id'],
                                    'unit_id': unit_id,
                                    'service_type':serverType
                                }).then(insertId => {
                                    return insertId;
                                }).catch(error => {
                                  console.error(error); // 异步操作失败，输出错误信息
                                });
                        }
                    }


                }
            }

            // for (let key1 in resData.orgMap) {
            //       if (resData.orgMap.hasOwnProperty(key1)) { // 确保是对象自有属性，不是原型链上的属性
            //         console.log(key1, resData.orgMap[key1]);
            //         let checkResult = await connection.query(`select * from m_unit where code = '${key1}'`);
            //         if(checkResult.length>0){
            //             console.error('数据已存在', resData.orgMap[key1]);
            //         }else{
            //             await asySql("INSERT INTO m_unit SET  ?",{
            //                 'area_id' : result[i]['id'],
            //                 'code' : key1,
            //                 'name' : resData.orgMap[key1]
            //             }).then(insertId => {
            //                 return insertId;
            //             }).catch(error => {
            //               console.error(error); // 异步操作失败，输出错误信息
            //             });
            //         }
            //       }
            // }
            
            await delay(500);
        }
            
    }
        
          
process.exit(0);  
}

async function getMatterInfo(){
    let sql=`select * from m_matter where id > 885 AND implement_subject is null`;
    let result=await connection.query(sql);
    for (var i = 0; i < result.length; i++) {
        var ip=getRndIP();
        var agent=Agent[rnd(0,Agent.length-1)];
        agent=Agent[0];
        var option={
            "Host":"www.lnzwfw.gov.cn",
            "User-Agent":agent,
            "Referer":'https://www.lnzwfw.gov.cn/',
            "X-Forwarded-For":ip,
        }
        let url=`https://zwfw.dl.gov.cn/dlPortal/item/toDetails/${result[i]['ITEM_ID']}`;
        console.log(result[i]['id'],result[i]['name'],url);
        let res = await requestWithRetry(url, 3, 5000,option).then(response => {
            return response;
          }).catch(error => {
            console.error('Error:', error);
          });
        let body=res.text;
        var $ = cheerio.load(body);

        //事项类型
        let event_type = $('.table-bordered .long-text:eq(2)').text().trim();
        if(event_type=='行政许可'){
            event_type = 1;     
        }else if(event_type=='行政给付'){
            event_type = 2;
        }else if(event_type=='行政确认'){
            event_type = 3;
        }else if(event_type=='行政奖励'){
            event_type = 4;
        }else if(event_type=='行政裁决'){
            event_type = 5;
        }else if(event_type=='行政备案'){
            event_type = 6;
        }else if(event_type=='其它行政权力'||event_type=='其他行政权力'){
            event_type = 7;
        }else if(event_type=='公共服务'){
            event_type = 8;
        }else{
            console.error('未找到事项类型：'+event_type)
            return;
        }
        let data = {}
        if(event_type==4){
            //行政奖励事项
            //办件类型
            let processing_type = $('.table-bordered .long-text:eq(9)').text().trim();
            if(processing_type=='承诺件'){
                processing_type = 1;
            }else if(processing_type=='即办件'){
                processing_type = 2;
            }else{
                console.error('未找到办件类型：'+processing_type)
                return;
            }
            //服务对象
            let service_object = $('.table-bordered .long-text:eq(8)').text().trim();
            service_object = service_object.split(",");
            for (var s_i = 0; s_i < service_object.length; s_i++) {
                if(service_object[s_i] == '自然人'){
                    service_object[s_i] = 1;
                }else if(service_object[s_i]=='事业法人'){
                    service_object[s_i] = 2;
                }else if(service_object[s_i]=='企业法人'){
                    service_object[s_i] = 3;
                }else if(service_object[s_i]=='非法人企业'){
                    service_object[s_i] = 4;
                }else if(service_object[s_i]=='行政机关'){
                    service_object[s_i] = 5;
                }else if(service_object[s_i]=='社会组织法人'){
                    service_object[s_i] = 6;
                }else if(service_object[s_i]=='其它组织'||service_object[s_i]=='其他组织'){
                    service_object[s_i] = 7;
                }else{
                    console.error('未找到服务对象：'+service_object[s_i])
                    return;
                }
            }
            service_object = service_object.join(",");
            
            data = {
                    'event_type':event_type,
                    'implement_subject':$('.table-bordered .long-text:eq(3)').text().trim(),
                    'type':$('.table-bordered .long-text:eq(3)').text().trim(),
                    'power_source':$('.table-bordered .long-text:eq(4)').text().trim(),
                    'event_version': $('.table-bordered .long-text:eq(5)').text().trim(),
                    'implement_subject_code':$('.table-bordered .long-text:eq(6)').text().trim(),
                    'service_object': service_object,
                    'processing_type':processing_type,
                    'is_acceptance_deficiency':$('.table-bordered .cell-break:eq(10)').text().trim(),
                    'is_proof_items':$('.table-bordered .cell-break:eq(11)').text().trim(),
                    'is_inform':$('.table-bordered .cell-break:eq(12)').text().trim(),
                }

        }else{
            //办件类型
            let processing_type = $('.table-bordered .long-text:eq(5)').text().trim();
            if(processing_type=='承诺件'){
                processing_type = 1;
            }else if(processing_type=='即办件'){
                processing_type = 2;
            }else{
                console.error('未找到办件类型：'+processing_type)
                return;
            }
            //服务对象
            let service_object = $('.table-bordered .long-text:eq(8)').text().trim();
            service_object = service_object.split(",");
            for (var s_i = 0; s_i < service_object.length; s_i++) {
                if(service_object[s_i] == '自然人'){
                    service_object[s_i] = 1;
                }else if(service_object[s_i]=='事业法人'){
                    service_object[s_i] = 2;
                }else if(service_object[s_i]=='企业法人'){
                    service_object[s_i] = 3;
                }else if(service_object[s_i]=='非法人企业'){
                    service_object[s_i] = 4;
                }else if(service_object[s_i]=='行政机关'){
                    service_object[s_i] = 5;
                }else if(service_object[s_i]=='社会组织法人'){
                    service_object[s_i] = 6;
                }else if(service_object[s_i]=='其它组织'||service_object[s_i]=='其他组织'){
                    service_object[s_i] = 7;
                }else{
                    console.error('未找到服务对象：'+service_object[s_i])
                    return;
                }
            }
            service_object = service_object.join(",");
            //是否支持预约办理
            let is_reservation = $('.table-bordered .long-text:eq(12)').text().trim();
            if(is_reservation=='是'){
                is_reservation = 1;
            }else if(is_reservation=='否'){
                is_reservation = 0;
            }else{
                console.error('未找到是否支持预约办理：'+is_reservation)
                return;
            }
            //是否支持物流快递
            let is_express_delivery = $('.table-bordered .long-text:eq(14)').text().trim();
            if(is_express_delivery=='是'){
                is_express_delivery = 1;
            }else if(is_express_delivery=='否'){
                is_express_delivery = 0;
            }else{
                console.error('未找到是否支持物流快递：'+is_express_delivery)
                return;
            }
            //行使层级
            let exercise_level = $('.table-bordered .long-text:eq(15)').text().trim();
            exercise_level = exercise_level.split(",");
            
            for (var e_i = 0; e_i < exercise_level.length; e_i++) {
                if(exercise_level[e_i]=='国家级'){
                    exercise_level[e_i] = 1;
                }else if(exercise_level[e_i]=='省级'||exercise_level[e_i]=='省级/直属'){
                    exercise_level[e_i] = 2;
                }else if(exercise_level[e_i]=='市级'||exercise_level[e_i]=='市级/隶属'){
                    exercise_level[e_i] = 3;
                }else if(exercise_level[e_i]=='县级'){
                    exercise_level[e_i] = 4;
                }else if(exercise_level[e_i]=='镇级'){
                    exercise_level[e_i] = 5;
                }else if(exercise_level[e_i]=='村级'){
                    exercise_level[e_i] = 6;
                }else{
                    console.error('未找到行使层级：'+exercise_level[e_i])
                    return;
                }
            }
            exercise_level = exercise_level.join(",");

            //是否支持网上支付
            let is_online_payment = $('.table-bordered .long-text:eq(17)').text().trim();
            if(is_online_payment=='是'){
                is_online_payment = 1;
            }else if(is_online_payment=='否'){
                is_online_payment = 0;
            }else if(is_online_payment==''){
                is_online_payment = 0;
            }else{
                console.error('未找到是否支持网上支付：'+is_online_payment)
                return;
            }
            //实施主体性质
            let implement_subject_nature = $('.table-bordered .long-text:eq(19)').text().trim();
            if(implement_subject_nature=='法定机关'){
                implement_subject_nature = 1;
            }else if(implement_subject_nature=='授权组织'){
                implement_subject_nature = 2;
            }else if(implement_subject_nature=='受委托组织'){
                implement_subject_nature = 3;
            }else{
                console.error('未找到实施主体性质：'+implement_subject_nature)
                return;
            }
            //办理形式
            let application_form = $('.table-bordered .long-text:eq(20)').text().trim();
            application_form = application_form.split(",");
            for (var a_i = 0; a_i < application_form.length; a_i++) {
                if(application_form[a_i] == '网上办理'){
                    application_form[a_i] = 1;
                }else if(application_form[a_i]=='窗口办理'){
                    application_form[a_i] = 2;
                }else if(application_form[a_i]=='快递申请'){
                    application_form[a_i] = 3;
                }else{
                    continue;
                    console.error('未找到办理形式：'+application_form[a_i])
                    return;
                }
            }
            application_form = application_form.join(",");
            //是否进驻政务大厅
            let is_entry = $('.table-bordered .long-text:eq(28)').text().trim();
            if(is_entry=='是'){
                is_entry = 1;
            }else if(is_entry=='否'){
                is_entry = 0;
            }else{
                continue;
                console.error('未找到是否进驻政务大厅：'+is_entry)
                return;
            }
            data = {
                    'event_type':event_type,
                    'type':$('.table-bordered .long-text:eq(3)').text().trim(),
                    'power_source':$('.table-bordered .long-text:eq(4)').text().trim(),
                    'processing_type':processing_type,
                    'implement_subject':$('.table-bordered .long-text:eq(7)').text().trim(),
                    'service_object': service_object,
                    'event_version': $('.table-bordered .long-text:eq(9)').text().trim(),
                    'scope_application':$('.table-bordered .long-text:eq(10)').text().trim(),
                    'implement_subject_code':$('.table-bordered .long-text:eq(11)').text().trim(),
                    'is_reservation':is_reservation,
                    'according_to':$('.table-bordered .long-text:eq(13)').text().trim(),
                    'is_express_delivery':is_express_delivery,
                    'exercise_level':exercise_level,
                    'joint_agency':$('.table-bordered .long-text:eq(16)').text().trim(),
                    'is_online_payment':is_online_payment,
                    'limitation':$('.table-bordered .long-text:eq(18)').text().trim(),
                    'implement_subject_nature':implement_subject_nature,
                    'application_form':application_form,
                    'intermediary_services':$('.table-bordered .long-text:eq(21)').text().trim(),
                    'legal_time':$('.table-bordered .long-text:eq(22)').text().trim(),
                    'system':$('.table-bordered .long-text:eq(23)').text().trim(),
                    'legal_time_unit':$('.table-bordered .long-text:eq(24)').text().trim(),
                    'arrive_num':$('.table-bordered .long-text:eq(25)').text().trim(),
                    'promise_time':$('.table-bordered .long-text:eq(26)').text().trim(),
                    'promise_time_unit':$('.table-bordered .long-text:eq(27)').text().trim(),
                    'is_entry':is_entry,
                    'prohibit':$('.table-bordered .long-text:eq(29)').text().trim(),
                    'acceptance_institution':$('.table-bordered .long-text:eq(30)').text().trim(),
                    'acceptance_institution_nature':$('.table-bordered .long-text:eq(31)').text().trim(),
                    'decision_institution':$('.table-bordered .long-text:eq(32)').text().trim(),
                    'decision_institution_nature':$('.table-bordered .long-text:eq(33)').text().trim(),
                    'publicity':$('.table-bordered .cell-break:eq(34)').text().trim(),
                    'query':$('.table-bordered .cell-break:eq(35)').text().trim(),
                    'suit_scope':$('.table-bordered .cell-break:eq(36)').text().trim(),
                    'approval_content':$('.table-bordered .cell-break:eq(37)').text().trim(),
                    'is_acceptance_deficiency':$('.table-bordered .cell-break:eq(38)').text().trim(),
                    'is_proof_items':$('.table-bordered .cell-break:eq(39)').text().trim(),
                    'is_inform':$('.table-bordered .cell-break:eq(40)').text().trim(),
                    'authority_division':$('.table-bordered .cell-break:eq(41)').text().trim(),
                }
        }
        
        await asySql(`update m_matter SET ? where id = '${result[i]['id']}'`,data).then(insertId => {
            return insertId;
        }).catch(error => {
          console.error(error); // 异步操作失败，输出错误信息
        });

        //通用情形
        let case_id = 0;
        let result_case=await connection.query(`select id from m_matter_case WHERE matter_id='${result[i]["id"]}'`);
        if(result_case.length>0){
            case_id = result_case[0]['id'];
        }else{
            case_id = await asySql("INSERT INTO m_matter_case SET  ?",{
                        'pid':0,
                        'matter_id':result[i]['id'],
                        'name':'通用情形'
                    }).then(insertId => {
                        return insertId;
                    }).catch(error => {
                      console.error(error); // 异步操作失败，输出错误信息
                    });
        }
        
        if(case_id>0){
            //材料列表
            let workApplyData = JSON.parse($('script:eq(18)').text().slice(18).slice(0,-1));
            for (var w = 0; w < workApplyData.length; w++) {
                let sql=`select * from m_matter_material WHERE name='${workApplyData[w].MaterialName}' AND case_id='${case_id}'`;
                let result_material=await connection.query(sql);
                if(result_material.length>0){
                    console.log('材料已录入',case_id,workApplyData[w].MaterialName);
                }else{
                    //来源渠道
                    let source = workApplyData[w].SourceType;
                    let source_info = '';
                    if(source=='申请人自备'){
                        source = 1;
                    }else if(source=='政府部门核发'){
                        source = 2;
                    }else if(source=='其他'){
                        source = 3;
                    }else{
                        console.log('找不到来源渠道',source)
                        return;
                    }

                    //类型
                    let type = workApplyData[w].MaterialType;
                    if(type=='原件'){
                        type = 1;
                    }else if(type=='复印件'){
                        type = 2;
                    }else if(type=='原件和复印件'){
                        type = 3;
                    }else if(type=='电子版'){
                        type = 4;
                    }else{
                        console.log('找不到类型',type)
                        return;
                    }

                    //形式
                    let shape = workApplyData[w].MaterialFormat;
                    if(shape=='纸质'){
                        shape = 1;
                    }else if(shape=='电子'){
                        shape = 2;
                    }else if(shape=='纸质、电子'||shape=='纸质和电子'){
                        shape = 3;
                    }else if(shape=='纸质或电子'){
                        shape = 4;
                    }else if(shape==''){
                        shape = null;
                    }else{
                        console.log('找不到形式',shape)
                        return;
                    }

                    //必要性
                    let necessity = workApplyData[w].IsNeed;
                    if(necessity=='必要'){
                        necessity = 1;
                    }else if(necessity=='非必要'){
                        necessity = 2;
                    }else if(necessity=='信用免交、信用后补'){
                        necessity = 3;
                    }else if(necessity=='容缺后补'){
                        necessity = 4;
                    }else{
                        console.log('找不到必要性',necessity)
                        return;
                    }

                    let addSql="INSERT INTO m_matter_material SET  ?";
                    let addSqlParams = {
                        'case_id':case_id,
                        'MaterialCode':workApplyData[w].TaskGuid,
                        'name':workApplyData[w].MaterialName,
                        'type':type,
                        'shape':shape,
                        'number':workApplyData[w].PageNum,
                        'copy_number':workApplyData[w].PageNum,
                        'necessity':necessity,
                        'source':source,
                        'standard':workApplyData[w].FillExplain,
                        'is_inform':workApplyData[w].IF_GZCN
                    };
                    await asySql(addSql,addSqlParams).then(insertId => {
                        return insertId;
                    }).catch(error => {
                      console.error(error); // 异步操作失败，输出错误信息
                    });
                }
            }
        }

        //常见问题
        if($('#work-cjwt .work-box-content:eq(0) p').length > 0){
            let question = $('#work-cjwt .work-box-content p:eq(0)').text().trim().substring(3);
            let answer = $('#work-cjwt .work-box-content p:eq(1)').text().trim().substring(3);
            let result_question = await connection.query(`select id from m_matter_question WHERE matter_id='${result[i]["id"]}'`);
            if(result_question.length>0){
                console.log('常见问题已录入')
            }else{
                await asySql(`INSERT INTO m_matter_question SET ?`,{
                    'matter_id':result[i]["id"],
                    'questions':question,
                    'answers':answer
                }).then(insertId => {
                    return insertId;
                }).catch(error => {
                  console.error(error); // 异步操作失败，输出错误信息
                });
            }
        }
                        
    await delay(1000);
    }
    process.exit(0); 
}

//插入数据库
async function asySql(addSql,addSqlParams){
    return await new Promise(resolve => {
        connection.query(addSql,addSqlParams,function (err, result) {
            if(err){
             console.log('[INSERT ERROR] - ',err.message);
             return;
            }else{
                console.log('INSERT ID:',result.insertId);
                return resolve(result.insertId);
            }        
            
        });
    })
        
}


//大连甘井子区 34 金普新区40
//辽宁省区域
// getArea();

//部门
// getUnit();

//大连甘井子区 区级事项列表
// getMatterList();
//事项详情
getMatterInfo();